﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Assessment.Management.SharedComponent.Params;
using Assessment.Management.SharedComponent.Entities;
using Assessment.Management.SharedComponent.Params.MenuManagers;
using Assessment.Management.SharedComponent.Constants;
using System.Data.SqlClient;

namespace Assessment.Management.DAO.AdminManagers
{
    public class AccountDAO : BaseDaos
    {
        public AccountDAO()
            : base()
        {
        }

        public void GetAccount(AccountParam param)
        {

            string query = "Select * from Accounts where Username = @Username and Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Username", param.Account.Username);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Account = base.ExecuteQueryGetObject<Account>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetPermission(AccountParam param)
        {

            string query = "Select * from Accounts where Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            //cmd.Parameters.AddWithValue("@Username", param.Account.Username);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listAccount = base.ExecuteQueryGetListObject<Account>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CheckAccount(AccountParam param)
        {

            string query = "Select * from Accounts where Username = @Username and Password =@Password and Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Username", param.Account.Username);
            cmd.Parameters.AddWithValue("@Password", param.Account.Password);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Account = base.ExecuteQueryGetObject<Account>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetAccountToEditByCode(AccountParam param)
        {
            string query = "Select * from Accounts where Deleted=@Deleted and Username=@Username";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@Username", param.Account.Username);

            try
            {
                param.Account = base.ExecuteQueryGetObject<Account>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CreateAccount(AccountParam param)
        {
            string query = @"Insert into Accounts (Username, Password, Permission,Email, CreatedBy, CreatedDTG, Deleted, Version) 
                                        values(@Username, @Password, @Permission,@Email, @CreatedBy, @CreatedDTG, @Deleted, @Version)";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Username", param.Account.Username);
            cmd.Parameters.AddWithValue("@Password", param.Account.Password);
            cmd.Parameters.AddWithValue("@Permission", param.Account.Permission);
            cmd.Parameters.AddWithValue("@Email", param.Account.Email);
            cmd.Parameters.AddWithValue("@CreatedBy", param.Account.CreatedBy);
            cmd.Parameters.AddWithValue("@CreatedDTG", param.Account.CreatedDTG);
            cmd.Parameters.AddWithValue("@Deleted", param.Account.Deleted);
            cmd.Parameters.AddWithValue("@Version", param.Account.Version);

            try
            {
                int count = base.ExecuteNonQuery(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void UpdateAccount(AccountParam param, SqlCommand sqlCmd = null)
        {
            string query = @"Update Accounts 
                              Set Password        = @Password,
                                Email=@Email,
                                Permission        = @Permission,  
                                Version        = Version+1,
                                UpdatedBy      = @UpdatedBy,
                                UpdatedDTG     = @UpdatedDTG
                            where Username         = @Username and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }

            sqlCmd.CommandText = query;
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@Username", param.Account.Username);
            sqlCmd.Parameters.AddWithValue("@Password", param.Account.Password);
            sqlCmd.Parameters.AddWithValue("@Permission", param.Account.Permission);
            sqlCmd.Parameters.AddWithValue("@Email", param.Account.Email);
            sqlCmd.Parameters.AddWithValue("@UpdatedBy", param.Account.UpdatedBy);
            sqlCmd.Parameters.AddWithValue("@UpdatedDTG", param.Account.UpdatedDTG);
            sqlCmd.Parameters.AddWithValue("@Version", param.Account.Version);


            try
            {
                int count = base.ExecuteNonQuery(sqlCmd);
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void DeleteAccounts(AccountParam param, SqlCommand sqlCmd = null)
        {
            var query = @"Update Accounts
                          Set Deleted        = @Deleted,
                              Version        = Version+1,
                              UpdatedBy      = @UpdatedBy,
                              UpdatedDTG     = @UpdatedDTG
                          Where Username = @Username and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }
            List<Account> lstDeleteAccount = param.listAccount;
            sqlCmd.CommandText = query;

            try
            {
                foreach (Account item in lstDeleteAccount)
                {
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Parameters.AddWithValue("@Deleted", item.Deleted);
                    sqlCmd.Parameters.AddWithValue("@Version", item.Version);
                    sqlCmd.Parameters.AddWithValue("@UpdatedBy", item.UpdatedBy);
                    sqlCmd.Parameters.AddWithValue("@UpdatedDTG", item.UpdatedDTG);
                    sqlCmd.Parameters.AddWithValue("@Username", item.Username);
                    int count = base.ExecuteNonQuery(sqlCmd);
                }
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void GetLastAccountCode(AccountParam param)
        {
            
            string query = "select top 1 username from Classes where Deleted=@Deleted order by CreatedDTG desc";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Account = base.ExecuteQueryGetObject<Account>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CheckExitsAccData(AccountParam param)
        {
            string query = @"select count(*) from Accounts 
                                             where Deleted   = @Deleted and 
                                                   Username = @Username";
                                                 
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@Username", param.Account.Username);
            try
            {
                param.Account.Username = base.ExecuteScalar(cmd).ToString();
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }
    }
}
